/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.pojo.CTDeal;
import model.util.connect;

/**
 *
 * @author ChiTuan
 */
public class DSSanphamDAO {
    
    public static ArrayList<CTDeal> laydssanpham(){
        ArrayList<CTDeal> ds = new ArrayList<CTDeal>();
        try{
//            int sosanphamtrentrang=9;
//            int batdau=(trang-1)*sosanphamtrentrang;
            String sql=String.format("Select * From Deal" );
            connect cn=new connect();
            cn.Open();   
            ResultSet rs=cn.executeQuery(sql);
            while(rs.next()){
                CTDeal ctd=new CTDeal();
                ctd.setIdDeal(rs.getInt("Id"));
                ctd.setTenDeal(rs.getString("TenDeal"));
                ctd.setGiaGoc(rs.getBigDecimal("GiaGoc"));
                ctd.setGiaGiam(rs.getBigDecimal("GiaGiam"));
                ctd.setPhanTramGiam(rs.getFloat("PhanTramGiam"));
                ctd.setSoPhieuToiThieu(rs.getInt("SoPhieuToiThieu"));
                ctd.setSoPhieuToiDa(rs.getInt("SoPhieuToiDa"));
                ctd.setSoPhieuDaBan(rs.getInt("SoPhieuDaBan"));
                ctd.setSoPhieuConLai(rs.getInt("SoPhieuConLai"));
                ctd.setThoiGianBatDau(rs.getDate("ThoiGianBatDau"));
                ctd.setThoiGianKetThuc(rs.getDate("ThoiGianKetThuc"));
                ctd.setIdCongTy(rs.getInt("IdCongTy"));
                ctd.setIdTinhThanh(rs.getInt("IdTinhThanh"));
                ctd.setSoNguoiMua(rs.getInt("SoNguoiMua"));
                ctd.setMota(rs.getString("Mota"));
                ctd.setSoBinhChon(rs.getInt("BinhChon"));
                ctd.setSoQuangCao(rs.getInt("LuotQuangCao"));
                ctd.setHinhAnh(rs.getString("HinhAnh"));
                ds.add(ctd);
            }
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
        return ds;
    }
    public static int laysoluongsanpham() throws ClassNotFoundException, SQLException{
            int soluong=-1;
            connect cn=new connect();
            cn.Open();
            String sql=String.format("Select count(*) as soluong From Deal");
            ResultSet rs=cn.executeQuery(sql);
            if(rs.next()){
                    soluong=rs.getInt("soluong");
                }
            return soluong;
             }
   public static int tinhsoluongtrang() throws ClassNotFoundException, SQLException{
        int sotrang;
        int soluongsanphamtrentrang=9;
        int soluong=laysoluongsanpham();
        sotrang=soluong/soluongsanphamtrentrang;
        if(soluong%soluongsanphamtrentrang!=0){
            sotrang++;
        }
        return sotrang;
    }

     public static ArrayList<CTDeal> layspcophantrang(int trang) throws ClassNotFoundException{
        ArrayList<CTDeal> ds=new ArrayList<CTDeal>();
        try{
            int sosanphamtrentrang=9;
            int batdau=(trang-1)*sosanphamtrentrang;
            int ketthuc=batdau+9;
            String sql=String.format("SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY Id) as row FROM Deal ) a WHERE row > %d and row <= %d",batdau,ketthuc );
            connect cn=new connect();
            cn.Open();   
            ResultSet rs=cn.executeQuery(sql);
            while(rs.next()){
                CTDeal ctd=new CTDeal();
                ctd.setIdDeal(rs.getInt("Id"));
                ctd.setTenDeal(rs.getString("TenDeal"));
                ctd.setGiaGoc(rs.getBigDecimal("GiaGoc"));
                ctd.setGiaGiam(rs.getBigDecimal("GiaGiam"));
                ctd.setPhanTramGiam(rs.getFloat("PhanTramGiam"));
                ctd.setSoPhieuToiThieu(rs.getInt("SoPhieuToiThieu"));
                ctd.setSoPhieuToiDa(rs.getInt("SoPhieuToiDa"));
                ctd.setSoPhieuDaBan(rs.getInt("SoPhieuDaBan"));
                ctd.setSoPhieuConLai(rs.getInt("SoPhieuConLai"));
                ctd.setThoiGianBatDau(rs.getDate("ThoiGianBatDau"));
                ctd.setThoiGianKetThuc(rs.getDate("ThoiGianKetThuc"));
                ctd.setIdCongTy(rs.getInt("IdCongTy"));
                ctd.setIdTinhThanh(rs.getInt("IdTinhThanh"));
                ctd.setSoNguoiMua(rs.getInt("SoNguoiMua"));
                ctd.setMota(rs.getString("Mota"));
                ctd.setSoBinhChon(rs.getInt("BinhChon"));
                ctd.setSoQuangCao(rs.getInt("LuotQuangCao"));
                ctd.setHinhAnh(rs.getString("HinhAnh"));
                ds.add(ctd);
            }
            cn.Close();
        }
        catch(SQLException ex){System.out.println(ex);}
        return ds;
            
        }
}
